import os
import sys
sys.path.extend([os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),'data_analysis'),os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),'modules')])
# print(sys.path)
from PIL import Image
import readconfig
import cus_data
import get_data
from datetime import datetime,date
import time
from dateutil.relativedelta import relativedelta
import re
import threading
from multiprocessing import Value
import xlwings as xw
import pandas as pd
pd.set_option('display.unicode.east_asian_width', True) #设置输出右对齐
# pd.set_option('display.max_columns', None) #显示所有列
from flask import Flask, request, jsonify,render_template,session,redirect,url_for,send_file
import pymysql
import datetime
from decimal import Decimal
import json
import random
import hashlib
import base64
from werkzeug.utils import secure_filename

class LzService(Flask):

    def __init__(self,*args,**kwargs):
        super(LzService, self).__init__(*args, **kwargs)
        config_fn=os.path.join(os.path.join(os.path.dirname(__file__),'config','lz_service.config'))
        # self.config_lz=readconfig.exp_json2(config_fn)
        with open(config_fn,'r',encoding='utf-8') as f:
            self.config_lz=json.load(f)
        # print(self.config_lz)
        self.app_lock=threading.Lock()
        self.cus_id_prefix=self.config_lz['cus_id_prefix']

        #显示薪酬统计录入页面
        self.add_url_rule('/ins_salary', view_func=self.ins_salary_page,methods=['GET','POST'])  
        #显示薪酬报告页面
        self.add_url_rule('/get_ins_salary_report_page', view_func=self.get_ins_salary_report_page,methods=['GET','POST'])  

         #从,'03-教练管理','教练资料','教练信息.xlsx'中获取教练列表
        self.add_url_rule('/get_ins_list', view_func=self.get_ins_list_db,methods=['GET','POST'])


        #返回有效的小团课清单
        self.add_url_rule('/get_group_cus_list', view_func=self.get_group_cus_list,methods=['GET','POST'])  
        #写入团课小清单的上课内容等
        self.add_url_rule('/deal_group_class',view_func=self.deal_group_class,methods=['GET','POST'])
        #获取薪酬基础参数
        self.add_url_rule('/get_salary_params',view_func=self.get_salary_params,methods=['GET','POST'])
        #新增写入薪酬结算表 
        self.add_url_rule('/write_cal_ins_salary',view_func=self.write_cal_ins_salary,methods=['GET','POST'])
        #修改写入薪酬结算表结算标记 
        self.add_url_rule('/update_cal_ins_salary',view_func=self.update_cal_ins_salary,methods=['GET','POST'])
        #根据前端传来的月份获取相应的薪酬表
        self.add_url_rule('/get_ins_salary',view_func=self.get_ins_salary,methods=['GET','POST'])


        #保存salary report数据
        self.add_url_rule('/save_salary_report',view_func=self.save_salary_report,methods=['GET','POST'])
        #查询salary report 
        self.add_url_rule('/get_ins_salary_report',view_func=self.get_ins_salary_report,methods=['GET','POST'])




    def connect_mysql(self):
        with open(os.path.join(os.path.dirname(os.path.realpath((__file__))),'config','db.config'),'r',encoding='utf-8') as f:
            cfg=json.load(f)
        # 连接数据库
        conn = pymysql.connect(
        host=cfg['host'],       # 数据库主机地址
        user=cfg['user'],     # 数据库用户名
        password=cfg['password'], # 数据库密码
        database=cfg['database'],  # 要连接的数据库名称
        port=cfg['port']
        )

        return conn
    
    def get_ins_salary_report(self):
        data = request.json
        print('getting salary report data...')
        # print('Received get_ins_salary_report data:', data)
       
        conn = self.connect_mysql()
        cursor = conn.cursor()

        try:
            month = data['month'] + '-01'  # 格式化为YYYY-MM-01
            ins_id = data['ins_id']
            print(f'Querying saved report for ins_id: {ins_id}, month: {month}')

            # 1. 查询报告基本信息
            sql_report = '''
                SELECT 
                    id, ins_name, basic_salary, basic_comment,
                    private_class_total, group_class_total, sales_total, sales_range,total
                FROM salary_save_reports
                WHERE ins_id = %s AND month = %s
            '''
            cursor.execute(sql_report, (ins_id, month))
            report = cursor.fetchone()

            if not report:
                return jsonify({
                    'res': 'failed',
                    'error': '未找到该月份的薪酬报告'
                })

            report_id = report[0]
            result = {
                'ins_name': report[1],
                'basic_salary': float(report[2]),
                'basic_comment': report[3],
                'private_class_total': float(report[4]),
                'group_class_total': float(report[5]),
                'sales_total': float(report[6]),
                'sales_range': report[7],
                'total': float(report[8]),
                'month': data['month'],
                'ins_id': ins_id
            }

            # 2. 查询私教课详情
            sql_private = '''
                SELECT 
                    card_id, cls_type, cls_name, pay,real_pay,cus_name,
                    cls_count, buy_date, commission
                FROM salary_save_private_details
                WHERE report_id = %s
                ORDER BY buy_date
            '''
            cursor.execute(sql_private, (report_id,))
            private_details = []
            for row in cursor.fetchall():
                private_details.append({
                    'card_id': row[0],
                    'class_type': row[1],
                    'class_name': row[2],
                    'pay':float(row[3]),
                    'real_pay':float(row[4]),
                    'cus_name': row[5],
                    'class_count': row[6],
                    'buy_date': row[7].strftime('%Y-%m-%d') if row[5] else None,
                    'commission': float(row[8])
                })
            result['private_class_details'] = private_details

            # 3. 查询小团课详情
            sql_group = '''
                SELECT 
                    member, cls_type, cls_name,
                    cls_time, cls_count, commission
                FROM salary_save_group_details
                WHERE report_id = %s
                ORDER BY cls_time
            '''
            cursor.execute(sql_group, (report_id,))
            group_details = []
            for row in cursor.fetchall():
                group_details.append({
                    'member': row[0],
                    'class_type': row[1],
                    'class_name': row[2],
                    'cls_time': row[3].strftime('%Y-%m-%d') if row[3] else None,
                    'cls_count': row[4],
                    'commission': float(row[5])
                })
            result['group_class_details'] = group_details

            # 4. 查询销售详情
            sql_sales = '''
                SELECT 
                    cus_name, buy_date, card_id,
                    pay_amount, real_pay, comment, commission
                FROM salary_save_sales_details
                WHERE report_id = %s
                ORDER BY buy_date
            '''
            cursor.execute(sql_sales, (report_id,))
            sales_details = []
            for row in cursor.fetchall():
                sales_details.append({
                    'cus_name': row[0],
                    'buy_date': row[1].strftime('%Y-%m-%d') if row[1] else None,
                    'card_id': row[2],
                    'pay_amount': float(row[3]),
                    'real_pay': float(row[4]),
                    'comment': row[5],
                    'commission': float(row[6])
                })
            result['sales_details'] = sales_details

            # 5. 查询未结算课程详情
            sql_unsettled = '''
                SELECT 
                    card_id, cls_type, cls_name,
                    cus_name, buy_date, start_date, days_since_start
                FROM salary_save_unsettled_details
                WHERE report_id = %s
                ORDER BY buy_date
            '''
            cursor.execute(sql_unsettled, (report_id,))
            unsettled_details = []
            for row in cursor.fetchall():
                unsettled_details.append({
                    'card_id': row[0],
                    'cls_type': row[1],
                    'cls_name': row[2],
                    'cus_name': row[3],
                    'buy_date': row[4].strftime('%Y-%m-%d') if row[4] else None,
                    'start_date': row[5].strftime('%Y-%m-%d') if row[5] else None,
                    'days_since_start': row[6]
                })
            result['unsettled_details'] = unsettled_details

            return jsonify({
                'res': 'ok',
                'data': result
            })

        except Exception as e:
            print('get_ins_salary_report error:', e)
            return jsonify({
                'res': 'failed',
                'error': str(e)
            })

        finally:
            cursor.close()
            conn.close()
    
    def save_salary_report(self):
        data = request.json
        print('saving salary report...')
        print('received save salary data:', data)
        conn = self.connect_mysql()
        cursor = conn.cursor()

        try:
            # 开始事务
            conn.begin()
            
            # 更新薪酬结算标记
            update_result = self.update_cal_ins_salary_internal(cursor, data)
            
            # 准备月份数据(格式化为YYYY-MM-01)
            month = data['month'] + '-01'
            ins_id = data['insId']
            
            # 1. 检查主表记录是否存在
            cursor.execute(
                "SELECT id FROM salary_save_reports WHERE ins_id = %s AND month = %s",
                (ins_id, month)
            )
            report = cursor.fetchone()
            
            if report:
                # 更新主表记录
                report_id = report[0]
                cursor.execute('''
                    UPDATE salary_save_reports SET
                        ins_name = %s,
                        basic_salary = %s,
                        basic_comment = %s,
                        private_class_total = %s,
                        group_class_total = %s,
                        sales_total = %s,
                        sales_range = %s,
                        total = %s
                    WHERE id = %s
                ''', (
                    data['insName'],
                    data['basicSalary'],
                    data['basicComment'],
                    data['privateClassTotal'],
                    data['groupClassTotal'],
                    data['salesTotal'],
                    data['salesRange'],
                    data['total'],
                    report_id
                ))
                
                # 删除旧的详情记录
                cursor.execute("DELETE FROM salary_save_private_details WHERE report_id = %s", (report_id,))
                cursor.execute("DELETE FROM salary_save_group_details WHERE report_id = %s", (report_id,))
                cursor.execute("DELETE FROM salary_save_sales_details WHERE report_id = %s", (report_id,))
                cursor.execute("DELETE FROM salary_save_unsettled_details WHERE report_id = %s", (report_id,))  # 新增：删除未结算记录
            else:
                # 插入新主表记录
                cursor.execute('''
                    INSERT INTO salary_save_reports (
                        ins_id, ins_name, month, basic_salary, basic_comment,
                        private_class_total, group_class_total, sales_total, sales_range,total
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,%s)
                ''', (
                    ins_id,
                    data['insName'],
                    month,
                    data['basicSalary'],
                    data['basicComment'],
                    data['privateClassTotal'],
                    data['groupClassTotal'],
                    data['salesTotal'],
                    data['salesRange'],
                    data['total']
                ))
                report_id = cursor.lastrowid
            
            # 2. 插入私教课详情
            for item in data.get('privateClassDetails', []):
                cursor.execute('''
                    INSERT INTO salary_save_private_details (
                        report_id, card_id, cls_type, cls_name, pay,real_pay,per_class_price,cus_name,
                        cls_count, buy_date, commission
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s)
                ''', (
                    report_id,
                    item['cardId'],
                    item['clsType'],
                    item['clsName'],
                    item['pay'],
                    item['real_pay'],
                    item['perClassPrice'],
                    item['cusName'],
                    item['clsCount'],
                    item['buyDate'],
                    float(item['commission'].strip())
                ))
            
            # 3. 插入小团课详情
            for item in data.get('groupClassDetails', []):
                cursor.execute('''
                    INSERT INTO salary_save_group_details (
                        report_id, member, cls_type, cls_name,
                        cls_time, cls_count, commission
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s)
                ''', (
                    report_id,
                    item['member'],
                    item['clsType'],
                    item['clsName'],
                    item['clsTime'],
                    item['clsCount'],
                    float(item['commission'])
                ))
            
            # 4. 插入销售详情
            for item in data.get('salesDetails', []):
                cursor.execute('''
                    INSERT INTO salary_save_sales_details (
                        report_id, cus_name, buy_date, card_id,
                        pay_amount, real_pay, comment, commission
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ''', (
                    report_id,
                    item['cusName'],
                    item['buyDate'],
                    item['cardId'],
                    float(item['payAmount']),
                    float(item['realPay']),
                    item['comment'],
                    float(item['commission'])
                ))
            
            # 5. 新增：插入未结算课程详情
            for item in data.get('unsettledDetails', []):
                cursor.execute('''
                    INSERT INTO salary_save_unsettled_details (
                        report_id, card_id, cls_type, cls_name,
                        cus_name, buy_date, start_date, days_since_start
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ''', (
                    report_id,
                    item['card_id'],
                    item['cls_type'],
                    item['cls_name'],
                    item['cus_name'],
                    item['buy_date'],
                    item['start_date'] if item['start_date'] != 'None' else None,
                    item['days_since_start']
                ))
            
            # 提交事务
            conn.commit()
            return jsonify({
                'res': 'ok', 
                'msg': '报告保存成功',
                'update_result': update_result
            })
            
        except Exception as e:
            conn.rollback()
            print('保存报告失败:', e)
            return jsonify({'res': 'failed', 'msg': str(e)})
        
        finally:
            cursor.close()
            conn.close()
    
    def get_group_cus_list(self):
        conn=self.connect_mysql()
        cursor=conn.cursor()

        try:
            #插入购课记录
            sql='''
                SELECT 
                    cct.cus_id,
                    cit.cus_name,
                    cit.sex,
                    ct.card_id,
                    ct.card_start_time,
                    ct.end_time,
                    ct.card_type,
                    ct.card_name,
                    ct.cls_qty,
                    COALESCE(ctrt.cls_tkn_qty, 0) AS cls_tkn_qty, 
                    ct.cls_qty - COALESCE(ctrt.cls_tkn_qty, 0) AS cls_qty_remain 
                FROM 
                    cards_table ct
                LEFT JOIN 
                    cardholder_card_table cct ON cct.card_id = ct.card_id
                LEFT JOIN 
                    cus_info_table cit ON cit.cus_id = cct.cus_id
                LEFT JOIN (
                    SELECT 
                        card_id,
                        COUNT(*) AS cls_tkn_qty 
                    FROM 
                        cls_tkn_rec_table 
                    GROUP BY 
                        card_id
                ) AS ctrt ON ctrt.card_id = ct.card_id
                WHERE 
                    ct.card_type = '限时课程'
                    AND ct.card_name = '小团课'
                    AND ct.end_time > CURDATE() 
                    AND ct.card_start_time IS NOT NULL;  
            '''
            
            cursor.execute(sql)
            res=cursor.fetchall()
   
            if res:
       
                cols=['cus_id','cus_name','sex','card_id','card_start_time','end_time','card_type','card_name','cls_qty','cls_tkn_qty','cls_qty_remain']
                df=pd.DataFrame(res,columns=cols)
 
                # 将日期字段转换为日期类型

                df['end_time'] = pd.to_datetime(df['end_time'])
              
                # print(self.dic_format2(df,cols,transfer_int_64='yes'))
                return jsonify({'res':'ok','data':self.dic_format2(df,cols,transfer_int_64='yes'),'cols':cols})
  
            else:
                return jsonify({'res':'failed','error':'no group class data'})

        except Exception as e:
            print('get group class data error:',e)
            return jsonify({'res':'failed','error':str(e)})
        
        finally:
            cursor.close()
            conn.close()
    
    def deal_group_class(self):
        data=request.json
        print(data)
        cuses=data['members']
        train_items=data['trainings']
        ins=data['coach']
        cls_datetime=data['trainingDateTime']
        opr_id=data['opr_id']
        cls_cmt=data['classComment']
        calories=data['caloriesBurned']

        # {'memberIds': ['LZ00101'],
        #   'trainings': [{'name': '小团课训练', 'category': '有氧训练', 'muscle': '复合肌群', 'equipment': '综合'}], 
        #   'coachId': 'LZINS999', 
        #   'trainingDateTime': '2025-05-28T19:29'}

        conn=self.connect_mysql()
        cursor=conn.cursor()

        try:
            opr_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            for cus in cuses:
                sql_cls_tkn='''
                    insert into cls_tkn_rec_table
                    (cus_id,cus_name,cls_datetime,cls_long,cls_type,cls_name,card_id,ins_id,ins_name,comment,operator_id,operate_time) 
                    values 
                    (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                '''
                values=(cus['id'],cus['name'],cls_datetime,1,'限时课程','小团课',cus['card_id'],ins['id'],ins['name'],cls_cmt,opr_id,opr_time)
                cursor.execute(sql_cls_tkn,values)

                values_oxy=[]
                for train in train_items:
                    print('train data:',train)
                    #有氧训练写入
                    oxy_train_sql='''
                        insert into train_oxy_rec_table 
                        (train_datetime,cus_id,cus_name,oxy_name,oxy_time,oxy_group,calories,card_id,ins_id,ins_name,comment) 
                        values 
                        (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
                    '''
                    # print(cls_datetime,cus['id'],cus['name'],train['name'],60,1,calories,cus['card_id'],ins['id'],ins['name'],cls_cmt)
                    values_oxy.append((cls_datetime,cus['id'],cus['name'],train['name'],60,1,calories,cus['card_id'],ins['id'],ins['name'],cls_cmt))
                
                cursor.executemany(oxy_train_sql,values_oxy)

            conn.commit()
            print('小团课录入成功')
            return jsonify({'res':'ok'})
        except Exception as e:
            return jsonify({'res':'failed','error':str(e)})
        finally:
            cursor.close()
            conn.close()

    
    def get_ins_salary(self):
        data=request.json
        print('get ins salary data Received data:',data)
        conn = self.connect_mysql()
        cursor = conn.cursor()
        ins_id=data['ins_id']
        year_month=data['month']

        #私教课查询
        try:
            sql='''
                SELECT DISTINCT 
                    ctrt.card_id,
                    ctrt.cls_type,
                    ctrt.cls_name,
                    ctrt.cus_id,
                    ctrt.cus_name,
                    ctrt.ins_id,
                    ctrt.ins_name,
                    COUNT(ctrt.card_id) AS cls_tkn_qty,
                    ct.card_start_time AS card_start_time,
                    DATEDIFF(LAST_DAY(STR_TO_DATE(CONCAT(%s, '-01'), '%%Y-%%m-%%d')), ct.card_start_time)+1 AS days_from_start,
                    brt.pay,
                    brt.real_pay,
                    brt.pay/brt.buy_num AS avr_pay,
                    brt.buy_date,
                    css.cal_salary 
                FROM cls_tkn_rec_table ctrt 
                LEFT JOIN(
                    SELECT DISTINCT card_id, card_start_time 
                    FROM cards_table ct0 
                    WHERE card_start_time IS NOT NULL
                ) AS ct ON ct.card_id = ctrt.card_id
                LEFT JOIN buy_rec_table brt ON brt.card_id = ctrt.card_id
                LEFT JOIN card_salary_stat css ON css.card_id = ctrt.card_id
                WHERE 
                    ctrt.ins_id = %s 
                    AND ctrt.cls_datetime BETWEEN STR_TO_DATE(CONCAT(%s, '-01'), '%%Y-%%m-%%d') AND LAST_DAY(STR_TO_DATE(CONCAT(%s, '-01'), '%%Y-%%m-%%d'))
                    AND ctrt.cls_type = '限时课程' AND ctrt.cls_name <> '小团课'
                GROUP BY 
                    ctrt.card_id, ctrt.cus_id, ctrt.cus_name, ctrt.ins_id, ctrt.ins_name,
                    ct.card_start_time, brt.pay, brt.real_pay, ctrt.cls_type, ctrt.cls_name,
                    brt.buy_date, brt.buy_num, css.cal_salary
                HAVING days_from_start >= 10

                UNION

                SELECT DISTINCT 
                    ctrt.card_id,
                    ctrt.cls_type,
                    ctrt.cls_name,
                    ctrt.cus_id,
                    ctrt.cus_name,
                    ctrt.ins_id,
                    ctrt.ins_name,
                    COUNT(ctrt.card_id) AS cls_tkn_qty,
                    ct.card_start_time AS card_start_time,
                    DATEDIFF(LAST_DAY(STR_TO_DATE(CONCAT(%s, '-01'), '%%Y-%%m-%%d')), ct.card_start_time)+1 AS days_from_start,
                    brt.pay,
                    brt.real_pay,
                    brt.pay/brt.buy_num AS avr_pay,
                    brt.buy_date,
                    css.cal_salary 
                FROM cls_tkn_rec_table ctrt 
                LEFT JOIN(
                    SELECT DISTINCT card_id, card_start_time 
                    FROM cards_table ct0 
                    WHERE card_start_time IS NOT NULL
                ) AS ct ON ct.card_id = ctrt.card_id
                LEFT JOIN buy_rec_table brt ON brt.card_id = ctrt.card_id
                LEFT JOIN card_salary_stat css ON css.card_id = ctrt.card_id
                WHERE 
                    ctrt.ins_id = %s 
                    AND ctrt.cls_datetime BETWEEN STR_TO_DATE(CONCAT(%s, '-01'), '%%Y-%%m-%%d') AND LAST_DAY(STR_TO_DATE(CONCAT(%s, '-01'), '%%Y-%%m-%%d'))
                    AND ctrt.cls_type = '常规课程'
                GROUP BY 
                    ctrt.card_id, ctrt.cus_id, ctrt.cus_name, ctrt.ins_id, ctrt.ins_name,
                    ct.card_start_time, brt.pay, brt.real_pay, ctrt.cls_type, ctrt.cls_name,
                    brt.buy_date, brt.buy_num, css.cal_salary

            '''


            cursor.execute(sql,(year_month,ins_id,year_month,year_month,year_month,ins_id,year_month,year_month))
            res=cursor.fetchall()


            sql='''
            select ins_id,ins_name,ins_type from ins_table where ins_id=%s;            
'''
            
            cursor.execute(sql,ins_id)
            res_ins=cursor.fetchone()

            #对全职兼职教练的底薪计算
            if res_ins[2]=='full':
                basic_salary=1500
            elif  res_ins[2]=='part':
                basic_salary=0
            else:
                basic_salary=0


            #查询本月销售数据
            sql='''
                select brt.cus_name,brt.buy_date,brt.card_id,brt.pay,brt.real_pay,ct.cashier_name,brt.comment from buy_rec_table brt
                left join cashier_table ct on ct.cashier_id=brt.cashier_id
                where year(brt.buy_date)=%s and month(brt.buy_date)=%s and brt.cashier_id=
                (select related_cashier_id from ins_table where ins_id=%s)

            '''
            cursor.execute(sql,(year_month.split('-')[0],year_month.split('-')[1],ins_id))
            res_sale=cursor.fetchall()

            if res_sale:
                res_sale_data=self.res_to_dic(res_sale,('cus_name','buy_date','card_id','pay','real_pay','cashier_name','comment'))


            #查询小团课数据
            sql='''
                SELECT  GROUP_CONCAT(DISTINCT ctrt.cus_name ORDER BY ctrt.cus_name SEPARATOR ', ') AS grp_cus_names,ctrt.cls_type,
                '小团课' as cls_name, ctrt.cls_datetime,COUNT(DISTINCT ctrt.cls_datetime) AS cls_tkn_qty,90 as avr_pay_for_ins, ctrt.ins_id,ctrt.ins_name
                FROM cls_tkn_rec_table ctrt
                WHERE 
                    ctrt.ins_id = %s
                    AND YEAR(ctrt.cls_datetime) = %s
                    AND MONTH(ctrt.cls_datetime) =%s
                    AND ctrt.cls_name = '小团课'
                GROUP by ctrt.cls_datetime,ctrt.cls_type,ctrt.cls_name,ctrt.ins_id,ctrt.ins_name

            '''
            cursor.execute(sql,((ins_id,year_month.split('-')[0],year_month.split('-')[1])))
            _res_grp=cursor.fetchall()
            print(_res_grp)


            if _res_grp:
                cols=('grp_cus_name','cls_type','cls_name','cls_datetime','cls_tkn_qty','avr_pay_for_ins','ins_id','ins_name')
                res_grp=self.res_to_dic(_res_grp,cols)
            else:
                res_grp=None


            if res and res_ins:
                cols=('card_id','cls_type','cls_name','cus_id','cus_name','ins_id','ins_name','cls_tkn_qty','card_start_time','days_from_start','pay','real_pay','avr_pay','buy_date','cal_salary')
                return jsonify({'res':'ok','msg':'获取教练薪酬列表成功','data':self.res_to_dic(res,cols),
                                'data_ins':{'ins_id':res_ins[0],'ins_name':res_ins[1],'ins_type':res_ins[2],'basic_salary':basic_salary},
                                'data_grp':res_grp,
                                'ins_sale_data':res_sale_data})
            else:
                return jsonify({
                'res': 'failed',
                'msg': 'get ins salary data error.'
            })
            
        except Exception as e:
            conn.rollback()
            print('get ins salary data error:', e)
            return jsonify({
                'res': 'failed',
                'msg': str(e)
            })
            
        finally:
            cursor.close()
            conn.close()

    def update_cal_ins_salary_internal(self, cursor, data):
        """供save_salary_report内部调用的更新方法"""
        success_count = 0
        failed_records = []
        
        try:
            # 处理结算课程
            if 'privateClassDetails' in data:
                for item in data['privateClassDetails']:
                    try:
                        cursor.execute('''
                        UPDATE card_salary_stat 
                        SET cal_salary = 'yes',
                            operator_id = %s,
                            operate_time = %s
                        WHERE card_id = %s
                        ''', (data['insId'], datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), item['cardId']))
                        success_count += 1
                    except Exception as e:
                        failed_records.append({
                            'card_id': item.get('cardId', 'N/A'),
                            'error': str(e)
                        })
                        print(f'Update failed for card_id {item.get("cardId")}: {e}')

            # 处理未结算课程
            if 'unsettledDetails' in data:
                for item in data['unsettledDetails']:
                    try:
                        cursor.execute('''
                        UPDATE card_salary_stat 
                        SET cal_salary = 'no',
                            operator_id = %s,
                            operate_time = %s
                        WHERE card_id = %s
                        ''', (data['insId'], datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), item['card_id']))
                        success_count += 1
                    except Exception as e:
                        failed_records.append({
                            'card_id': item.get('card_id', 'N/A'),
                            'error': str(e)
                        })
                        print(f'Update failed for card_id {item.get("card_id")}: {e}')
            
            return {
                'updated_count': success_count,
                'failed_count': len(failed_records),
                'failed_records': failed_records
            }
                
        except Exception as e:
            raise Exception(f'更新薪酬结算标记失败: {str(e)}'a)


    def write_cal_ins_salary(self):

        dat=request.json

        dat['cus_id']=dat['客户编码及姓名'][:7].strip()
        dat['cus_name']=dat['客户编码及姓名'][7:].strip()

        del dat['客户编码及姓名']
        data_cols=['cus_id','cus_name','收款日期', '购课卡号','购课流水号','购课类型','购课名称','购课节数', '购课时长（天）', '应收金额', '实收金额','与应收一致', '收款人', '收入类别', '备注','管理员备注','operatorId','operateTime']
        sorted_data={key: dat[key] for key in data_cols}
        values_buy_table=tuple(sorted_data.values()) 
        
        print('salary mark received data:',sorted_data)

        conn=self.connect_mysql()
        cursor=conn.cursor()

        print(sorted_data['cus_id'],sorted_data['cus_name'],sorted_data['收款日期'],sorted_data['购课卡号'],'no',sorted_data['购课流水号'],
                                sorted_data['购课类型'],sorted_data['购课名称'],sorted_data['收款人'],'',sorted_data['operatorId'],sorted_data['operateTime'])

        try:
            #插入购课记录
            sql='''
                insert into card_salary_stat 
                (cus_id,cus_name,buy_date,card_id,cal_salary,buy_flow_id,buy_type,buy_cls_name,cashier_id,comment,operator_id,operate_time)
                values
                (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                '''
            
            cursor.execute(sql,(sorted_data['cus_id'],sorted_data['cus_name'],sorted_data['收款日期'],sorted_data['购课卡号'],'no',sorted_data['购课流水号'],
                                sorted_data['购课类型'],sorted_data['购课名称'],sorted_data['收款人'],'',sorted_data['operatorId'],sorted_data['operateTime']))
            conn.commit()
            
            return jsonify({'res':'ok','msg':'新增购课记录插入薪酬未结算标记no成功'})

        except Exception as e:
            print('insert into card_salary_stat error:',e)
            return jsonify({'res':'failed','error':str(e)})
        
        finally:
            cursor.close()
            conn.close()


    def get_salary_params(self):
        with open(os.path.join(os.path.dirname((__file__)),'config','salary.config'),'r',encoding='utf-8') as f:
            cfg=json.load(f)

        return jsonify(cfg)
    
    def ins_salary_page(self):
        session_name=session.get('user')
        session_role=session.get('role')
        session_id=session.get('ins_id')
        return render_template('./ins_salary.html',session_ins_name=session_name,session_role=session_role,session_ins_id=session_id)
    
    def get_ins_salary_report_page(self):
        session_name=session.get('user')
        session_role=session.get('role')
        session_id=session.get('ins_id')
        return render_template('./get_ins_salary_report.html',session_ins_name=session_name,session_role=session_role,session_ins_id=session_id)

    
    def get_ins_list_db(self):
         # 创建一个游标对象
        print('from llz database,get ins list.')
        conn=self.connect_mysql()
        cursor = conn.cursor()

        result={}

        # 获取教练姓名，不包括管理员角色
        sql="select ins_id,ins_name from ins_table where role='ins' and account_status='on';"
        cursor.execute(sql)
        ins_res = cursor.fetchall()
        ins_list=[x[0]+x[1] for x in ins_res]
        result['ins_list']=ins_list

        cursor.close()
        conn.close()

        return jsonify(result)
    
    def res_to_dic(self,res,keys):
        dic_arr=[]
        for n,item in enumerate(res):
            dic={}
            for k,value in enumerate(item):
                dic[keys[k]]=value
            dic_arr.append(dic)
        return dic_arr


if __name__ == '__main__':
    app = LzService(__name__)
    app.secret_key='lz8888'
    if len(sys.argv)>1:
        print(f'服务器为：{sys.argv[1]}:5001')
        app.run(debug=True,host=sys.argv[1],port=5001)
    else:
        app.run(debug=True)
